package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import simorg.domain.ClassificationTest;
import simorg.domain.DerivativeTest;
import simorg.domain.Test;
import simorg.domain.TestType;
import simorg.util.DatabaseConnection;

public class TestTable {

	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		    	query = "DROP TABLE IF EXISTS TEST";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
		    }
		    query = "CREATE TABLE IF NOT EXISTS TEST" +
		    	" (ID INT PRIMARY KEY, TYPE VARCHAR(20), NAME VARCHAR(50), MINUTES INT, " + 
		    	" DESCRIPTION VARCHAR(255), DISPLAY_ORDER INT, WEIGHT DOUBLE, WEIGHTING_COMMENT VARCHAR(255))";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query);
		}
		finally {
			dc.getConnection().close();
		}
	}
	public static void save(DatabaseConnection dc, Test test) {
		try {
			String query = null;
			if ( findById(dc, test.getId()) == null ) {
				query = "INSERT INTO TEST (ID, TYPE, NAME, MINUTES, DESCRIPTION, DISPLAY_ORDER, WEIGHT, WEIGHTING_COMMENT) " +
								"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?)";
				PreparedStatement insert = dc.getConnection().prepareStatement(query);
				fillPreparedStatement(test, insert);
				insert.executeUpdate();
				ResultSet rs = insert.getGeneratedKeys();
				while (rs.next()){
					test.setId(rs.getInt(1));
				}
			}
			else {
				throw new RuntimeException("Tests are read-only");
				/*
				// update existing course
				query = "UPDATE TEST SET ID = ?, TYPE = ?, NAME = ?, MINUTES = ?, DESCRIPTION = ?, DISPLAY_ORDER = ?, WEIGHT = ?, WEIGHTING_COMMENT = ? " +
						"WHERE ID=?";
				PreparedStatement update = dc.getConnection().prepareStatement(query);
				fillPreparedStatement(test, update);
				update.setInt(9, test.getId());
				update.executeUpdate();*/
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static Test findById(DatabaseConnection dc, int id) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		ArrayList<Test> result = find(dc, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}
	
	public static Collection<Test>  findByType(DatabaseConnection dc, TestType type) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("TYPE", type.toString()));
		return find(dc, constraints);
	}
	
	protected static ArrayList<Test> find(DatabaseConnection dc, QueryConstraints constraints) {
		ArrayList<Test> retval = new ArrayList<Test>();
		try {
			String query = "SELECT * FROM TEST " + constraints.getParameterString();
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				Test test = null;
				TestType type = TestType.valueOf(rs.getString("TYPE"));
				if ( type == TestType.Classification ) test = new ClassificationTest();
				else test = new DerivativeTest();
				fillFromResultSet(test, rs);
		        retval.add(test);
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}
	
	public static void fillFromResultSet(Test a,ResultSet rs) throws Exception {
		a.setId(rs.getInt("ID"));
		a.setName(rs.getString("NAME"));
		a.setMinutes(rs.getInt("MINUTES"));
		a.setDescription(rs.getString("DESCRIPTION"));
		a.setOrder(rs.getInt("DISPLAY_ORDER"));
		a.setWeight(rs.getDouble("WEIGHT"));
		a.setWeightingComment(rs.getString("WEIGHTING_COMMENT"));
	}

	public static void fillPreparedStatement(Test a, PreparedStatement stmt) throws Exception {
		stmt.setInt(1, a.getId());
		stmt.setString(2, a.getType().toString());
		stmt.setString(3, a.getName());
		stmt.setInt(4, a.getMinutes());
		stmt.setString(5, a.getDescription());
		stmt.setInt(6, a.getOrder());
		stmt.setDouble(7, a.getWeight());
		stmt.setString(8, a.getWeightingComment());
	}
}
